//201902104030_何琛选
package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.Department;
import cn.edu.sdjzu.xg.bysj.domain.Major;
import cn.edu.sdjzu.xg.bysj.service.DepartmentService;
import cn.edu.sdjzu.xg.bysj.service.MajorService;
import util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.TreeSet;

public final class MajorDao {
    private static MajorDao majorDao = new MajorDao();
    private static Collection<Major> majors;

    private MajorDao() {
    }

    public static MajorDao getInstance() {
        if (majorDao == null) {
            majorDao = new MajorDao();
        }
        return majorDao;
    }


    public Collection<Major> findAll() throws SQLException {
        Collection<Major> majors = new TreeSet<Major>();
        Connection conn = JdbcHelper.getConn();
        String sql_findAll = "select * from Major";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(sql_findAll);
        //根据id查找Major
        ResultSet resultSet = pstmt.executeQuery();

        while (resultSet.next()) {
            //根据id查找结果创建Major对象
            Major major = new Major(
                    resultSet.getInt("id"),
                    resultSet.getString("description"),
                    resultSet.getString("no"),
                    resultSet.getString("remarks"),
                    DepartmentService.getInstance().find(
                            resultSet.getInt("department_id")));
            majors.add(major);
        }
        //关闭对象
        JdbcHelper.close(pstmt, conn);
        return majors;
    }

    public Major find(Integer id) throws SQLException{
        Connection conn = JdbcHelper.getConn();
        String sql_delete = "select * from Major where id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(sql_delete);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //根据id查找Major
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        //根据id查找结果创建Major对象
        Major department = new Major(id,
                resultSet.getString("description"),
                resultSet.getString("no"),
                resultSet.getString("remarks"),
                DepartmentService.getInstance().find(resultSet.getInt("department_id")));
        //关闭
        JdbcHelper.close(pstmt, conn);
        return department;
    }

    public boolean update(Major major) throws SQLException{
        Connection conn = JdbcHelper.getConn();
        String sql_update = "UPDATE Major SET no = ?,description = ?,remarks = ?,department_id = ? WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(sql_update);
        //为预编译参数赋值
        pstmt.setString(1, major.getNo());
        pstmt.setString(2, major.getDescription());
        pstmt.setString(3, major.getRemarks());
        pstmt.setInt(4, major.getDepartment().getId());
        pstmt.setInt(5, major.getId());

        //修改Major 并判断是否修改成功
        return pstmt.executeUpdate()>0;
    }

    public boolean add(Major major) throws SQLException{
        Connection conn = JdbcHelper.getConn();
        String sql_add = "INSERT INTO Major(description,no,remarks,department_id) VALUES"
                + "(?,?,?,?)";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(sql_add);
        //为预编译参数赋值
        pstmt.setString(1, major.getDescription());
        pstmt.setString(2, major.getNo());
        pstmt.setString(3, major.getRemarks());
        pstmt.setInt(4, major.getDepartment().getId());
        //添加Department 并判断是否添加成功
        return pstmt.executeUpdate()>0;
    }

    public boolean delete(Integer id) throws SQLException{
        //创建sql语句
        String sql_delete = "delete from Major where id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = JdbcHelper.getConn().prepareStatement(sql_delete);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        if(pstmt.executeUpdate() == 0){
            JdbcHelper.close(pstmt,JdbcHelper.getConn());
            return false;
        }else {
            JdbcHelper.close(pstmt,JdbcHelper.getConn());
            return  true;
        }
    }

    public boolean delete(Major major) throws SQLException{
        return this.delete(major.getId());
    }

    public static void main(String[] args) {
        try {
            //查找id=1的Department
            Department department = DepartmentService.getInstance().find(1);
            System.out.println(department);

            //测试增加
            Major major1 = new Major(2, "管理", "05", " ", department);
            System.out.println(MajorService.getInstance().add(major1));
            Major major2 = new Major(3, "艺术", "06", " ", department);
            System.out.println(MajorService.getInstance().add(major2));

            //查找id=2的Major
            System.out.println(MajorService.getInstance().find(2));

            //修改
            Major update_major = new Major(2, "管理", "05", "", department);
            System.out.println(MajorService.getInstance().update(update_major));

            //删除
            System.out.println(MajorService.getInstance().delete(2));

            //查找全部
            System.out.println(MajorService.getInstance().findAll());

        }
        catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
